﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;
using saigonbook.Biz;

namespace saigonbook.Dal
{
    public class DAL_sach
    {
        public static List<BIZ_sach> Select_sach()
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from sach");
            List<BIZ_sach> list = new List<BIZ_sach>();
            foreach (DataRow row in dt.Rows)
            {
                BIZ_sach sach = new BIZ_sach((int)row[0], DAL_sach.GetLoaiSachById((int)row[1]),
                            row[2].ToString(), row[3].ToString(), DAL_sach.GetNXBById((int)row[4]),
                            (int)row[5], (int)row[6], (int)row[7], (int)row[8],
                            row[9].ToString(), (DateTime)row[10], row[11].ToString());
                list.Add(sach);
            }
            return list;
        }

        public static List<BIZ_sach> Select_sach(BIZ_nhaxuatban nxb)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from sach where manhaxuatban = N'" + nxb.manhaxuatban + "'");
            List<BIZ_sach> list = new List<BIZ_sach>();
            foreach (DataRow row in dt.Rows)
            {
                BIZ_sach sach = new BIZ_sach((int)row[0], DAL_sach.GetLoaiSachById((int)row[1]),
                            row[2].ToString(), row[3].ToString(), DAL_sach.GetNXBById((int)row[4]),
                            (int)row[5], (int)row[6], (int)row[7], (int)row[8],
                            row[9].ToString(), (DateTime)row[10], row[11].ToString());
                list.Add(sach);
            }
            return list;
        }

        public static List<BIZ_sach> Select_sach(int maloai)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from sach where maloai = N'"+ maloai +"'");
            List<BIZ_sach> list = new List<BIZ_sach>();
            foreach (DataRow row in dt.Rows)
            {
                BIZ_sach sach = new BIZ_sach((int)row[0], DAL_sach.GetLoaiSachById((int)row[1]),
                            row[2].ToString(), row[3].ToString(), DAL_sach.GetNXBById((int)row[4]),
                            (int)row[5], (int)row[6], (int)row[7], (int)row[8],
                            row[9].ToString(), (DateTime)row[10], row[11].ToString());
                list.Add(sach);
            }
            return list;
        }

        public static List<BIZ_loaisach> Select_loaisach()
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from loaisach");
            List<BIZ_loaisach> list = new List<BIZ_loaisach>();
            foreach (DataRow row in dt.Rows)
            {
                BIZ_loaisach loaisach = new BIZ_loaisach((int)row[0], row[1].ToString());
                list.Add(loaisach);
            }
            return list;
        }

        public static List<BIZ_loaisach> Search_LoaiSach(string ten)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from LOAISACH where tenloai like N'%" + ten + "%'");
            List<BIZ_loaisach> list = new List<BIZ_loaisach>();

            foreach (DataRow row in dt.Rows)
            {
                BIZ_loaisach loaisach = new BIZ_loaisach((int)row[0], row[1].ToString());
                list.Add(loaisach);
            }
            return list;
        }

        public static List<BIZ_nhaxuatban> Select_NhaXuatBan()
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from NHAXUATBAN");
            List<BIZ_nhaxuatban> list = new List<BIZ_nhaxuatban>();

            foreach (DataRow row in dt.Rows)
            {
                BIZ_nhaxuatban nxb = new BIZ_nhaxuatban((int)row[0], row[1].ToString(), row[2].ToString(),
                                                        row[3].ToString(), row[4].ToString());
                list.Add(nxb);
            }
            return list;
        }

        public static List<BIZ_nhaxuatban> Search_NhaXuatBan(string ten)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from NHAXUATBAN where tennhaxuatban like N'%" + ten + "%'");
            List<BIZ_nhaxuatban> list = new List<BIZ_nhaxuatban>();

            foreach (DataRow row in dt.Rows)
            {
                BIZ_nhaxuatban nxb = new BIZ_nhaxuatban((int)row[0], row[1].ToString(), row[2].ToString(),
                                                        row[3].ToString(), row[4].ToString());
                list.Add(nxb);
            }
            return list;
        }

        public static BIZ_sach GetSachById(int masach)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from sach where masach='" + masach + "'");
            BIZ_sach result = null;
            foreach (DataRow row in dt.Rows)
            {
                result = new BIZ_sach((int)row[0], DAL_sach.GetLoaiSachById((int)row[1]),
                            row[2].ToString(), row[3].ToString(), DAL_sach.GetNXBById((int)row[4]),
                            (int)row[5], (int)row[6], (int)row[7], (int)row[8],
                            row[9].ToString(), (DateTime)row[10], row[11].ToString());
            }
            return result;
        }

        private static BIZ_loaisach GetLoaiSachById(int maloai)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from loaisach where maloai='" + maloai + "'");
            BIZ_loaisach result = null;
            foreach (DataRow row in dt.Rows)
            {
                result = new BIZ_loaisach((int)row[0], row[1].ToString());
            } 
            return result;
        }

        private static BIZ_nhaxuatban GetNXBById(int manhaxuatban)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from nhaxuatban where manhaxuatban='" + manhaxuatban + "'");
            BIZ_nhaxuatban result = null;
            foreach (DataRow row in dt.Rows)
            {
                result = new BIZ_nhaxuatban((int)row[0], row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString());
            }
            return result;
        }

        public static ComboBox cbb_sach(ComboBox cbb_sach)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from SACH");
            cbb_sach.DataSource = dt;
            cbb_sach.DisplayMember = "tensach";
            cbb_sach.ValueMember = "masach";
            return cbb_sach;
        }

        public static ComboBox cbb_loaisach(ComboBox cbb_loaisach)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from LOAISACH");
            cbb_loaisach.DataSource = dt;
            cbb_loaisach.DisplayMember = "tenloai";
            cbb_loaisach.ValueMember = "maloai";
            return cbb_loaisach;
        }

        public static ComboBox cbb_nhaxuatban(ComboBox cbnxb)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from NHAXUATBAN");
            cbnxb.DataSource = dt;
            cbnxb.DisplayMember = "tennhaxuatban";
            cbnxb.ValueMember = "manhaxuatban";
            return cbnxb;
        }

        public static void Them_sach(BIZ_sach dto)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Insert into sach(masach,maloai,tensach,tacgia,manhaxuatban,namxuatban,giabia,giaban,sotrang,hinhanh,ngaycapnhat,tomtatnoidung) VALUES (N'" + dto.masach + "',N'" + dto.loai.maloai + "',N'" + dto.tensach + "',N'" + dto.tacgia + "',N'" + dto.nhaxuatban.manhaxuatban + "',N'" + dto.namxuatban + "',N'" + dto.giabia + "',N'" + dto.giaban + "',N'" + dto.sotrang + "',N'" + dto.hinhanh + "',N'" + dto.ngaycapnhat.Date.ToString("MM-dd-yyyy") + "',N'" + dto.tomtatnoidung + "')");
        }
        public static void Sua_sach(BIZ_sach dto)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Update sach set maloai=N'" + dto.loai.maloai + "',tensach=N'" + dto.tensach + "',tacgia=N'" + dto.tacgia + "',manhaxuatban=N'" + dto.nhaxuatban.manhaxuatban + "',namxuatban=N'" + dto.namxuatban + "',giabia=N'" + dto.giabia + "',giaban=N'" + dto.giaban + "',sotrang=N'" + dto.sotrang + "',hinhanh=N'" + dto.hinhanh + "',ngaycapnhat=N'" + dto.ngaycapnhat.Date.ToString("MM-dd-yyyy") + "',tomtatnoidung=N'" + dto.tomtatnoidung + "' where masach= '" + dto.masach + "'");
        }
        public static void Xoa_sach(BIZ_sach dto)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Delete from sach where masach= '" + dto.masach + "'");
        }

        public static void Them_LoaiSach(BIZ_loaisach dto)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Insert into LOAISACH(maloai,tenloai) VALUES (N'" + dto.maloai + "',N'" + dto.tenloai + "')");
        }
        public static void Sua_LoaiSach(BIZ_loaisach dto)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Update LOAISACH set tenloai=N'" + dto.tenloai + "' where maloai= '" + dto.maloai + "'");
        }

        public static void Xoa_loaisach(BIZ_loaisach loai)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Delete from loaisach where maloai= '" + loai.maloai + "'");
        }

        public static void Them_NhaXuatBan(BIZ_nhaxuatban dto)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Insert into NHAXUATBAN(manhaxuatban,tennhaxuatban,diachi,dienthoai,email) VALUES (N'" + dto.manhaxuatban + "',N'" + dto.tennhaxuatban + "',N'" + dto.diachi + "',N'" + dto.sodienthoai + "',N'" + dto.email + "')");
        }

        public static void Sua_NhaXuatBan(BIZ_nhaxuatban dto)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Update NHAXUATBAN set tennhaxuatban=N'" + dto.tennhaxuatban + "',diachi=N'" + dto.diachi + "',dienthoai=N'" + dto.sodienthoai + "',email=N'" + dto.email + "'where manhaxuatban= '" + dto.manhaxuatban + "'");
        }

        public static void Xoa_nhaxuatban(BIZ_nhaxuatban nxb)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Delete from nhaxuatban where manhaxuatban= '" + nxb.manhaxuatban + "'");
        }
    }
}
